package dao.jdbc;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

import dao.StudentDAOI;
import dao.jdbc.util.DBConnectionFactory;
import dto.Student;

public class StudentDAO implements StudentDAOI{
	
	@Override
	public void saveStudent(Student s)
	{
		String sql = "INSERT INTO StudentDetails(fname, lname, dob, email, mobile, inserted_by, inserted_on VALUES(?,?,?,?,?,?,?)";
		
		Connection con = null;
		try {
			con = DBConnectionFactory.getConnection();
			PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			
			ps.setString(1, s.getFname());
			ps.setString(2, s.getLname());
			ps.setDate(3, new Date(s.getDob().getTime()));
			ps.setString(4, s.getEmail());
			ps.setString(5, s.getMobile());
			ps.setString(6, s.getInsertedBy());
			ps.setDate(7, new Date(s.getInsertedOn().getTime()));
			
			ResultSet rs = ps.getGeneratedKeys();
			rs.next();
			s.setSid(rs.getInt(1));
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				con.close();
			}catch(SQLException e) {}
		}
	}
	@Override
	public Student getStudent(int sid)
	{
		String sql = "SELECT fname, lname, dob, email, mobile, inserted_by, inserted_on FROM StudentDetails WHERE studentid = " + sid;
		
		Connection con = null;
		try {
			con = DBConnectionFactory.getConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(sql);
			if(rs.next())
			{
				Student s = new Student();
				s.setSid(sid);
				s.setFname(rs.getString(1));
				s.setLname(rs.getString(2));
				s.setDob(rs.getDate(3));
				s.setEmail(rs.getString(4));
				s.setMobile(rs.getString(5));
				s.setInsertedBy(rs.getString(6));
				s.setInsertedOn(rs.getDate(7));
				s.setUpdatedBy(rs.getString(8));
				s.setUpdateOn(rs.getDate(9));
				
				return s;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				con.close();
			}catch(SQLException e) {}
		}
		return null;
	}
	@Override
	public void updateStudent(Student s)
	{
		String sql = "UPDATE StudentDetails SET fname=?, lname=?, dob=?, email=?, mobile=?, updated_by=?, updated_on=? WHERE studentid=?";
		
		Connection con = null;
		try {
			con = DBConnectionFactory.getConnection();
			PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			
			ps.setString(1, s.getFname());
			ps.setString(2, s.getLname());
			ps.setDate(3, new Date(s.getDob().getTime()));
			ps.setString(4, s.getEmail());
			ps.setString(5, s.getMobile());
			ps.setString(6, s.getUpdatedBy());
			ps.setDate(7, new Date(s.getUpdatedOn().getTime()));
			ps.setInt(8, s.getSid());
			
			ps.executeUpdate();
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				con.close();
			}catch(SQLException e) {}
		}
	}
	@Override
	public Collection<Student> getAllStudentsByName(String namePattern)
	{
		namePattern = "%"+namePattern+"%";
		String sql = "SELECT fname, lname, dob, email, mobile, inserted_by, inserted_on, updated_by, updated_on, studentid FROM StudentDetails WHERE like '"+namePattern+"' or lname like '"+namePattern+"'";
		
		Connection con = null;
		try {
			con = DBConnectionFactory.getConnection();
			
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(sql);
			Collection<Student> students = new ArrayList<Student>();
			
			while(rs.next())
			{
				Student s = new Student();
				s.setFname(rs.getString(1));
				s.setLname(rs.getString(2));
				s.setDob(rs.getDate(3));
				s.setEmail(rs.getString(4));
				s.setMobile(rs.getString(5));
				s.setInsertedBy(rs.getString(6));
				s.setInsertedOn(rs.getDate(7));
				s.setUpdatedBy(rs.getString(8));
				s.setUpdateOn(rs.getDate(9));
			}
			
			return students;
		}catch(SQLException e){
			e.printStackTrace();
		}finally {
			try {
				con.close();
			}catch(SQLException e) {}
		}
		return null;
	}
}
